﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace ArcGisMapDesktop
{
    public class CommExportDataXls
    {
        #region 导出Excel
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T">泛型类名</typeparam>
        /// <param name="fileDepath">文件的物理路径</param>
        /// <param name="sColumnName">列名集合</param>
        /// <param name="sColumnWidth">列宽集合</param>
        /// <param name="dataSourceList">数据源</param>
        public bool ExportToExcel<T>(string fileDepath, List<T> dataSourceList, string[] sColumnName, int[] sColumnWidth = null,string fomrat= "yyyy/MM/dd")
        {
            bool result = false;
            try
            {
                DateTime dtTemp;
                //处理数据源
                ISheet sheet = null;
                IRow row = null;
                IWorkbook workbook = null;
                bool isExcel2003 = fileDepath.ToLower().EndsWith("xls") ? true : false;
                if (isExcel2003)
                {
                    workbook = new HSSFWorkbook();
                }
                else  
                {
                    workbook = new XSSFWorkbook();
                }
                sheet = workbook.CreateSheet("sheet1");
                //创建表头行
                row = sheet.CreateRow(0);//设置表头（第一行）
                for (int i = 0; i < sColumnName.Length; i++)
                {
                    row.CreateCell(i).SetCellValue(sColumnName[i]);
                }
                ICellStyle style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                style.WrapText = false;
                style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 11;
                font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                style.SetFont(font);//HEAD 样式
                row.Cells.ForEach(a => { a.CellStyle = style; });
                if (sColumnWidth != null)
                {
                    for (int j = 0; j < sColumnWidth.Length; j++)
                    {
                        sheet.SetColumnWidth(j, sColumnWidth[j] * 256);
                    }
                }
                for (int k = 0; k < dataSourceList.Count; k++)
                {
                    row = sheet.CreateRow(k + 1);//设置内容行（从第二行开始）
                    var model = dataSourceList[k];
                    System.Reflection.PropertyInfo[] properties = model.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
                    for (int l = 0; l < properties.Length; l++)
                    {
                        System.Reflection.PropertyInfo item = properties[l];
                        string name = item.Name; //名称
                        object value = item.GetValue(model, null);  //值
                        if (item.PropertyType.GetType() == typeof(DBNull))
                        {
                            value = "";
                        }

                        if (value == null)
                        {
                            row.CreateCell(l).SetCellValue("");
                        }
                        else if (DateTime.TryParse(value.ToString(), out dtTemp))
                        {
                            row.CreateCell(l).SetCellValue(dtTemp.ToString(fomrat));//处理时间
                        }
                        else
                        {
                            row.CreateCell(l).SetCellValue(value.ToString());
                        }
                    }
                }
                sheet.ForceFormulaRecalculation = true;
                using (FileStream fs = new FileStream(fileDepath, FileMode.Create, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);//写入并保存成Excel文件
                }
                //下载此Excel文件
                DownloadExcelFile(fileDepath);
                //删除Excel文件
                File.Delete(fileDepath);
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
               // Logs.Error("导出异常：" + ex.Message);
            }
            return result;
        }


        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="fileDepath">文件的物理路径</param>
        /// <param name="sColumnName">列名集合</param>
        /// <param name="startIndex">开始导入行 默认为0</param>
        /// <param name="dataSourceList">数据源 object必须要能转Dictionary<string, object></param>
        public bool ExportToExcel(string fileTemp, List<object> dataSourceList, string[] sColumnName, ref string eorrManage, int startIndex = 0)
        {
            bool result = false;
            try
            {
                DateTime dtTemp;
                //处理数据源
                ISheet sheet = null;
                IRow row = null;
                IWorkbook workbook;
                if (!File.Exists(fileTemp))
                    throw new Exception("找不到xls模板文件！");
                workbook= GetWorkBook(fileTemp);
                sheet = workbook.GetSheetAt(0);
                for (int k = 0; k < dataSourceList.Count; k++)
                {
                    row = sheet.CreateRow(k + 1 + startIndex);//设置内容行（从第二行开始）
                    var model = dataSourceList[k] as Dictionary<string, object>;
                    if (model == null)
                        throw new Exception(k + "行转换Dictionary<string, object>失败！");
                    int l = 0;
                    foreach (string str in sColumnName)
                    {

                        if (model.Keys.Contains(str))
                        {
                            if (model[str] == null)
                            {
                                row.CreateCell(l).SetCellValue("");
                            }
                            else if (DateTime.TryParse(model[str].ToString(), out dtTemp))
                            {
                                row.CreateCell(l).SetCellValue(dtTemp.ToString("yyyy/MM/dd HH:mm:ss"));//处理时间
                            }
                            else
                            {
                                row.CreateCell(l).SetCellValue(model[str].ToString());
                            }
                        }
                        else
                        {
                            row.CreateCell(l).SetCellValue("");
                        }
                        l++;
                    }
                }
                sheet.ForceFormulaRecalculation = true;
                string MapPath = HttpContext.Current.Server.MapPath("~/UploadFile");
                if (!File.Exists(MapPath))///如果服务器上面没有该路径 就创建该路径
                {
                    Directory.CreateDirectory(MapPath);
                }
                string newFilePath = MapPath + "/" + DateTime.Now.Ticks + ".xlsx";
                using (FileStream fs = new FileStream(newFilePath, FileMode.Create, FileAccess.ReadWrite))
                {
                    workbook.Write(fs);//写入并保存成Excel文件
                }
                FileInfo info = new FileInfo(fileTemp);
                //下载此Excel文件
                DownloadExcelFile(newFilePath, info.Name);
                //删除Excel文件
                File.Delete(newFilePath);
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
                eorrManage = "导出异常：" + ex.Message;
                //Logs.Error("导出异常：" + ex.Message);
            }
            return result;



        }
        #endregion
        IWorkbook GetWorkBook(string fileTemp)
        {
            IWorkbook workbook;
            FileInfo fileInfo = new FileInfo(fileTemp);
            FileStream file = new FileStream(fileTemp, FileMode.Open, FileAccess.Read);
            if (fileInfo.Extension == ".xls")
            {
                try
                {
                    workbook = new HSSFWorkbook(file);
                }
                catch (Exception ex)
                {
                    //Logs.Error(ex);
                    workbook = new XSSFWorkbook(file);
                }
            }
            else
            {
                workbook = new XSSFWorkbook(file);
            }
            return workbook;
        }
        #region  下载Excel文件
        /// <summary>
        /// 下载Excel文件
        /// </summary>
        /// <param name="filePath">服务器物理路径</param>
        public void DownloadExcelFile(string filePath,string filename="")
        {
            try
            {
                if (File.Exists(filePath))
                {
                    FileInfo info = new FileInfo(filePath);
                    if (string.IsNullOrEmpty(filename))
                        filename = info.Name;
                    long fileSize = info.Length;
                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.ContentType = "application/ms-excel";
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(filename));
                    //不指明Content-Length用Flush的话不会显示下载进度   
                    HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());
                    HttpContext.Current.Response.WriteFile(filePath, 0, fileSize);
                    HttpContext.Current.Response.Flush();
                }
            }
            catch
            { }
            finally
            {
                HttpContext.Current.Response.Close();
            }
        }
        #endregion

    }
}
